<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                   http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.4.xsd">
    <changeSet id="2022-04-05-init-sec-ddl" author="bchernysh">
        <!--sec_permissions-->
        <createTable tableName="sec_permissions">
            <column name="id" type="varchar(36)">
                <constraints primaryKey="true" primaryKeyName="sec_permissions_pk"/>
            </column>
            <column name="config_id" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
            <column name="name" type="varchar(50)">
                <constraints nullable="false" unique="true" uniqueConstraintName="sec_permissions_name_uk"/>
            </column>
            <column name="generation" type="int" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="major_rev" type="varchar(50)" defaultValue="A">
                <constraints nullable="false"/>
            </column>
            <column name="minor_rev" type="varchar(50)"/>
            <column name="is_current" type="tinyint" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="permission_id" type="varchar(36)">
                <constraints foreignKeyName="sec_permissions_permission_fk" references="sec_permissions(id)"/>
            </column>
            <column name="locale" type="varchar(50)">
                <constraints foreignKeyName="sec_permissions_locale_fk" references="core_locales(name)"/>
            </column>
            <column name="lifecycle_id" type="varchar(36)">
                <constraints foreignKeyName="sec_permissions_lifecycle_fk" references="core_lifecycles(id)"/>
            </column>
            <column name="state" type="varchar(50)"/>
            <column name="created_at" type="datetime">
                <constraints nullable="false"/>
            </column>
            <column name="created_by_id" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
            <column name="updated_at" type="datetime"/>
            <column name="updated_by_id" type="varchar(36)"/>
            <column name="locked_by_id" type="varchar(36)"/>
        </createTable>

        <!--sec_users-->
        <createTable tableName="sec_users">
            <column name="id" type="varchar(36)">
                <constraints primaryKey="true" primaryKeyName="sec_users_pk"/>
            </column>
            <column name="config_id" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
            <column name="username" type="varchar(50)">
                <constraints nullable="false" unique="true" uniqueConstraintName="sec_users_username_uk"/>
            </column>
            <column name="passwd" type="varchar(100)">
                <constraints nullable="false"/>
            </column>
            <column name="enabled" type="tinyint">
                <constraints nullable="false"/>
            </column>
            <column name="generation" type="int" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="major_rev" type="varchar(50)" defaultValue="A">
                <constraints nullable="false"/>
            </column>
            <column name="minor_rev" type="varchar(50)"/>
            <column name="is_current" type="tinyint" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="permission_id" type="varchar(36)">
                <constraints foreignKeyName="sec_users_permission_fk" references="sec_permissions(id)"/>
            </column>
            <column name="locale" type="varchar(50)">
                <constraints foreignKeyName="sec_users_locale_fk" references="core_locales(name)"/>
            </column>
            <column name="lifecycle_id" type="varchar(36)">
                <constraints foreignKeyName="sec_users_lifecycle_fk" references="core_lifecycles(id)"/>
            </column>
            <column name="state" type="varchar(50)"/>
            <column name="created_at" type="datetime">
                <constraints nullable="false"/>
            </column>
            <column name="created_by_id" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
            <column name="updated_at" type="datetime"/>
            <column name="updated_by_id" type="varchar(36)"/>
            <column name="locked_by_id" type="varchar(36)"/>
        </createTable>

        <!--sec_roles-->
        <createTable tableName="sec_roles">
            <column name="id" type="varchar(36)">
                <constraints primaryKey="true" primaryKeyName="sec_roles_pk"/>
            </column>
            <column name="config_id" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
            <column name="username" type="varchar(50)">
                <constraints nullable="false"/>
            </column>
            <column name="role" type="varchar(50)">
                <constraints nullable="false"/>
            </column>
            <column name="generation" type="int" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="major_rev" type="varchar(50)" defaultValue="A">
                <constraints nullable="false"/>
            </column>
            <column name="minor_rev" type="varchar(50)"/>
            <column name="is_current" type="tinyint" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="permission_id" type="varchar(36)">
                <constraints foreignKeyName="sec_roles_permission_fk" references="sec_permissions(id)"/>
            </column>
            <column name="locale" type="varchar(50)">
                <constraints foreignKeyName="sec_roles_locale_fk" references="core_locales(name)"/>
            </column>
            <column name="lifecycle_id" type="varchar(36)">
                <constraints foreignKeyName="sec_roles_lifecycle_fk" references="core_lifecycles(id)"/>
            </column>
            <column name="state" type="varchar(50)"/>
            <column name="created_at" type="datetime">
                <constraints nullable="false"/>
            </column>
            <column name="created_by_id" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
            <column name="updated_at" type="datetime"/>
            <column name="updated_by_id" type="varchar(36)"/>
            <column name="locked_by_id" type="varchar(36)"/>
        </createTable>

        <addUniqueConstraint tableName="sec_roles" constraintName="sec_roles_username_role_uk" columnNames="username, role"/>
        <addForeignKeyConstraint constraintName="sec_roles_username_fk" baseTableName="sec_roles" baseColumnNames="username"
                                 referencedTableName="sec_users" referencedColumnNames="username" onUpdate="CASCADE" onDelete="CASCADE"/>

        <!--sec_groups-->
        <createTable tableName="sec_groups">
            <column name="id" type="varchar(36)">
                <constraints primaryKey="true" primaryKeyName="sec_groups_pk"/>
            </column>
            <column name="config_id" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
            <column name="group_name" type="varchar(50)">
                <constraints nullable="false" unique="true" uniqueConstraintName="sec_groups_group_name_uk"/>
            </column>
            <column name="generation" type="int" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="major_rev" type="varchar(50)" defaultValue="A">
                <constraints nullable="false"/>
            </column>
            <column name="minor_rev" type="varchar(50)"/>
            <column name="is_current" type="tinyint" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="permission_id" type="varchar(36)">
                <constraints foreignKeyName="sec_groups_permission_fk" references="sec_permissions(id)"/>
            </column>
            <column name="locale" type="varchar(50)">
                <constraints foreignKeyName="sec_groups_locale_fk" references="core_locales(name)"/>
            </column>
            <column name="lifecycle_id" type="varchar(36)">
                <constraints foreignKeyName="sec_groups_lifecycle_fk" references="core_lifecycles(id)"/>
            </column>
            <column name="state" type="varchar(50)"/>
            <column name="created_at" type="datetime">
                <constraints nullable="false"/>
            </column>
            <column name="created_by_id" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
            <column name="updated_at" type="datetime"/>
            <column name="updated_by_id" type="varchar(36)"/>
            <column name="locked_by_id" type="varchar(36)"/>
        </createTable>

        <!--sec_group_roles-->
        <createTable tableName="sec_group_roles">
            <column name="id" type="varchar(36)">
                <constraints primaryKey="true" primaryKeyName="sec_group_roles_pk"/>
            </column>
            <column name="config_id" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
            <column name="group_id" type="varchar(36)">
                <constraints nullable="false" foreignKeyName="sec_group_roles_group_fk" references="sec_groups(id)"/>
            </column>
            <column name="role" type="varchar(50)">
                <constraints nullable="false"/>
            </column>
            <column name="generation" type="int" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="major_rev" type="varchar(50)" defaultValue="A">
                <constraints nullable="false"/>
            </column>
            <column name="minor_rev" type="varchar(50)"/>
            <column name="is_current" type="tinyint" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="permission_id" type="varchar(36)">
                <constraints foreignKeyName="sec_group_roles_permission_fk" references="sec_permissions(id)"/>
            </column>
            <column name="locale" type="varchar(50)">
                <constraints foreignKeyName="sec_group_roles_locale_fk" references="core_locales(name)"/>
            </column>
            <column name="lifecycle_id" type="varchar(36)">
                <constraints foreignKeyName="sec_group_roles_lifecycle_fk" references="core_lifecycles(id)"/>
            </column>
            <column name="state" type="varchar(50)"/>
            <column name="created_at" type="datetime">
                <constraints nullable="false"/>
            </column>
            <column name="created_by_id" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
            <column name="updated_at" type="datetime"/>
            <column name="updated_by_id" type="varchar(36)"/>
            <column name="locked_by_id" type="varchar(36)"/>
        </createTable>

        <addUniqueConstraint tableName="sec_group_roles" constraintName="sec_group_roles_group_role_uk" columnNames="group_id, role"/>

        <!--sec_group_members-->
        <createTable tableName="sec_group_members">
            <column name="id" type="varchar(36)">
                <constraints primaryKey="true" primaryKeyName="sec_group_members_pk"/>
            </column>
            <column name="config_id" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
            <column name="group_id" type="varchar(36)">
                <constraints nullable="false" foreignKeyName="sec_group_members_group_fk" references="sec_groups(id)"/>
            </column>
            <column name="username" type="varchar(50)">
                <constraints nullable="false"/>
            </column>
            <column name="generation" type="int" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="major_rev" type="varchar(50)" defaultValue="A">
                <constraints nullable="false"/>
            </column>
            <column name="minor_rev" type="varchar(50)"/>
            <column name="is_current" type="tinyint" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="permission_id" type="varchar(36)">
                <constraints foreignKeyName="sec_group_members_permission_fk" references="sec_permissions(id)"/>
            </column>
            <column name="locale" type="varchar(50)">
                <constraints foreignKeyName="sec_group_members_locale_fk" references="core_locales(name)"/>
            </column>
            <column name="lifecycle_id" type="varchar(36)">
                <constraints foreignKeyName="sec_group_members_fk" references="core_lifecycles(id)"/>
            </column>
            <column name="state" type="varchar(50)"/>
            <column name="created_at" type="datetime">
                <constraints nullable="false"/>
            </column>
            <column name="created_by_id" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
            <column name="updated_at" type="datetime"/>
            <column name="updated_by_id" type="varchar(36)"/>
            <column name="locked_by_id" type="varchar(36)"/>
        </createTable>

        <addUniqueConstraint tableName="sec_group_members" constraintName="sec_group_members_group_username_uk" columnNames="group_id, username"/>
        <addForeignKeyConstraint constraintName="sec_group_members_username_fk" baseTableName="sec_group_members" baseColumnNames="username"
                                 referencedTableName="sec_users" referencedColumnNames="username" onUpdate="CASCADE" onDelete="CASCADE"/>

        <!--sec_identities-->
        <createTable tableName="sec_identities">
            <column name="id" type="varchar(36)">
                <constraints primaryKey="true" primaryKeyName="sec_identities_pk"/>
            </column>
            <column name="config_id" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
            <column name="name" type="varchar(50)">
                <constraints nullable="false"/>
            </column>
            <column name="principal" type="tinyint">
                <constraints nullable="false"/>
            </column>
            <column name="generation" type="int" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="major_rev" type="varchar(50)" defaultValue="A">
                <constraints nullable="false"/>
            </column>
            <column name="minor_rev" type="varchar(50)"/>
            <column name="is_current" type="tinyint" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="permission_id" type="varchar(36)">
                <constraints foreignKeyName="sec_identities_permission_fk" references="sec_permissions(id)"/>
            </column>
            <column name="locale" type="varchar(50)">
                <constraints foreignKeyName="sec_identities_locale_fk" references="core_locales(name)"/>
            </column>
            <column name="lifecycle_id" type="varchar(36)">
                <constraints foreignKeyName="sec_identities_lifecycle_fk" references="core_lifecycles(id)"/>
            </column>
            <column name="state" type="varchar(50)"/>
            <column name="created_at" type="datetime">
                <constraints nullable="false"/>
            </column>
            <column name="created_by_id" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
            <column name="updated_at" type="datetime"/>
            <column name="updated_by_id" type="varchar(36)"/>
            <column name="locked_by_id" type="varchar(36)"/>
        </createTable>

        <addUniqueConstraint tableName="sec_identities" constraintName="sec_identities_name_principal_uk" columnNames="name, principal"/>

        <!--sec_access-->
        <createTable tableName="sec_access">
            <column name="id" type="varchar(36)">
                <constraints primaryKey="true" primaryKeyName="sec_access_pk"/>
            </column>
            <column name="config_id" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
            <column name="label" type="varchar(50)"/>
            <column name="sort_order" type="int"/>
            <column name="source_id" type="varchar(36)">
                <constraints nullable="false" foreignKeyName="sec_access_source_fk" references="sec_permissions(id)"/>
            </column>
            <column name="target_id" type="varchar(36)">
                <constraints nullable="false" foreignKeyName="sec_access_target_fk" references="sec_identities(id)"/>
            </column>
            <column name="mask" type="int" defaultValue="0">
                <constraints nullable="false"/>
            </column>
            <column name="granting" type="tinyint" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="begin_date" type="datetime">
                <constraints nullable="false"/>
            </column>
            <column name="end_date" type="datetime"/>
            <column name="generation" type="int" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="major_rev" type="varchar(50)" defaultValue="A">
                <constraints nullable="false"/>
            </column>
            <column name="minor_rev" type="varchar(50)"/>
            <column name="is_current" type="tinyint" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="permission_id" type="varchar(36)">
                <constraints foreignKeyName="sec_access_permission_fk" references="sec_permissions(id)"/>
            </column>
            <column name="locale" type="varchar(50)">
                <constraints foreignKeyName="sec_access_locale_fk" references="core_locales(name)"/>
            </column>
            <column name="lifecycle_id" type="varchar(36)">
                <constraints foreignKeyName="sec_access_lifecycle_fk" references="core_lifecycles(id)"/>
            </column>
            <column name="state" type="varchar(50)"/>
            <column name="created_at" type="datetime">
                <constraints nullable="false"/>
            </column>
            <column name="created_by_id" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
            <column name="updated_at" type="datetime"/>
            <column name="updated_by_id" type="varchar(36)"/>
            <column name="locked_by_id" type="varchar(36)"/>
        </createTable>

        <addUniqueConstraint tableName="sec_access" constraintName="sec_access_source_target_uk" columnNames="source_id, target_id"/>

        <createIndex tableName="sec_access" indexName="sec_access_idx">
            <column name="mask"/>
            <column name="granting"/>
            <column name="begin_date"/>
            <column name="end_date"/>
        </createIndex>

        <!--sec_allowed_permissions-->
        <createTable tableName="sec_allowed_permissions">
            <column name="id" type="varchar(36)">
                <constraints primaryKey="true" primaryKeyName="sec_allowed_permissions_pk"/>
            </column>
            <column name="config_id" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
            <column name="label" type="varchar(50)"/>
            <column name="sort_order" type="int"/>
            <column name="is_default" type="tinyint"/>
            <column name="source_id" type="varchar(36)">
                <constraints nullable="false" foreignKeyName="sec_allowed_permissions_source_fk" references="core_items(id)"/>
            </column>
            <column name="target_id" type="varchar(36)">
                <constraints nullable="false" foreignKeyName="sec_allowed_permissions_target_fk" references="sec_permissions(id)"/>
            </column>
            <column name="generation" type="int" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="major_rev" type="varchar(50)" defaultValue="A">
                <constraints nullable="false"/>
            </column>
            <column name="minor_rev" type="varchar(50)"/>
            <column name="is_current" type="tinyint" defaultValue="1">
                <constraints nullable="false"/>
            </column>
            <column name="permission_id" type="varchar(36)">
                <constraints foreignKeyName="sec_allowed_permissions_permission_fk" references="sec_permissions(id)"/>
            </column>
            <column name="locale" type="varchar(50)">
                <constraints foreignKeyName="sec_allowed_permissions_locale_fk" references="core_locales(name)"/>
            </column>
            <column name="lifecycle_id" type="varchar(36)">
                <constraints foreignKeyName="sec_allowed_permissions_lifecycle_fk" references="core_lifecycles(id)"/>
            </column>
            <column name="state" type="varchar(50)"/>
            <column name="created_at" type="datetime">
                <constraints nullable="false"/>
            </column>
            <column name="created_by_id" type="varchar(36)">
                <constraints nullable="false"/>
            </column>
            <column name="updated_at" type="datetime"/>
            <column name="updated_by_id" type="varchar(36)"/>
            <column name="locked_by_id" type="varchar(36)"/>
        </createTable>

        <addUniqueConstraint tableName="sec_allowed_permissions" constraintName="sec_allowed_permissions_source_target_uk" columnNames="source_id, target_id"/>
    </changeSet>

    <changeSet id="2023-04-24-user_session_data" author="bchernysh">
        <addColumn tableName="sec_users">
            <column name="session_data" type="text"/>
        </addColumn>
    </changeSet>
</databaseChangeLog>
